import socket,binascii,struct,time,threading,subprocess,platform,re,pymysql,xlrd
from queue import Queue
from concurrent.futures import ThreadPoolExecutor, wait, ALL_COMPLETED
class mysql_io:
    def __init__(self,host,user,passwd,db_name):
        self.host=host
        self.port=3306
        self.user=user
        self.password=passwd
        self.db_name=db_name

    #查询一条
    def select_one(self,sql):
        db=pymysql.connect(host=self.host,user=self.user,passwd=self.password,database=self.db_name)
        cursor=db.cursor()
        cursor.execute(sql)
        data=cursor.fetchone()
        db.close()
        return data
    #查询多条
    def select_all(self,sql):
        db=pymysql.connect(host=self.host,user=self.user,passwd=self.password,database=self.db_name)
        cursor=db.cursor()
        cursor.execute(sql)
        data=cursor.fetchall()
        db.close()
        return data

    #更新数据
    def update(self,sql):
        db=pymysql.connect(host=self.host,user=self.user,passwd=self.password,database=self.db_name)
        cursor=db.cursor()
        try:
            cursor.execute(sql)
            db.commit()
            return "更新成功"
        except:
            db.rollback()
        db.close()

    def insert(self,sql):
        db=pymysql.connect(host=self.host,user=self.user,passwd=self.password,database=self.db_name)
        cursor=db.cursor()
        try:
            cursor.execute(sql)
            db.commit()
            return "插入成功"
        except:
            db.rollback()
        db.close()

#网络唤醒
def format_mac(mac):
    mac_re = re.compile(r'''
                      (^([0-9A-F]{1,2}[-]){5}([0-9A-F]{1,2})$
                      |^([0-9A-F]{1,2}[:]){5}([0-9A-F]{1,2})$
                      |^([0-9A-F]{1,2}[.]){5}([0-9A-F]{1,2})$
                      )''', re.VERBOSE | re.IGNORECASE)
    # print(re.match(mac_re, mac))
    if re.match(mac_re, mac):
        if mac.count(':') == 5 or mac.count('-') == 5 or mac.count('.'):
            sep = mac[2]
            mac_fm = mac.replace(sep, '')
            return mac_fm
    else:
        raise ValueError('Incorrect MAC format')

#方法一
def create_magic_packet(mac):
    data = 'FF' * 6 + str(mac) * 16
    # print(data)
    # print(type(data))
    send_data = binascii.unhexlify(data)
    # print(type(send_data))
    return send_data

#方法二
def create_magic_packet0(mac):
     data = b'FF' * 6 + (mac * 16).encode()
     print(data)

     print(type(data))
     send_data = b''
     for i in range(0, len(data), 2):
      send_data = send_data + struct.pack(b'B', int(data[i: i + 2], 16)) # int(data[i: i+2], 16) 把16进制转换成整数
     print(type(send_data))
     return send_data


def send_magic_packet(send_data):
    broadcast_address = '255.255.255.255'
    port = 9
    s = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
    s.setsockopt(socket.SOL_SOCKET, socket.SO_BROADCAST, 1)
    s.sendto(send_data, (broadcast_address, port))

#唤醒主机
def netall(MAC):
    mac = format_mac(MAC)
    send_data = create_magic_packet0(mac)
    send_magic_packet(send_data)

#根据IP获取mac地址
def ip_getmac(ipaddr):
    try:
        aa=subprocess.check_output("ping -n 1 %s"%ipaddr).decode("GBK")
        ss=subprocess.check_output("arp -a %s"%ipaddr).decode("GBK")
        return ss.split(ipaddr)[1].strip()[0:17]
    except:
        return 0

#根据IP获取mac地址 并入库
def ip_getmac_mysql(ipaddr):
        aa=subprocess.check_output("ping -n 1 %s"%ipaddr).decode("GBK")
        ss=subprocess.check_output("arp -a %s"%ipaddr).decode("GBK")
        mac=ss.split(ipaddr)[1].strip()[0:17]
        sql='update ipdb1.tp_ipdb set mac="%s" where ip="%s";'%(mac,ipaddr)
        print (sql)
        my=mysql_io(host=host,user=user,passwd=passwd,db_name=dbname)
        ip_list=my.update(sql)
        return "更新成功"

#判断是否在线
def host_on_off(ip):
    os_type=platform.system()
    if os_type=='Windows':
        res = subprocess.call('ping -n 2 -w 5 %s' % ip,stdout=subprocess.PIPE)  # linux 系统将 '-n' 替换成 '-c',增加 'shell=True'
    else:
        res = subprocess.call('ping -c 2 -w 5 %s' % ip,shell=True,stdout=subprocess.PIPE)  # linux 系统将 '-n' 替换成 '-c',增加 'shell=True'
    return (res)

#单个主机测试是否在线  并且入库
def ping_ip(ip):
        os_type=platform.system()
        if os_type=='Windows':
            res = subprocess.call('ping -n 2 -w 5 %s' % ip,stdout=subprocess.PIPE)  # linux 系统将 '-n' 替换成 '-c',增加 'shell=True'
        else:
            res = subprocess.call('ping -c 2 -w 5 %s' % ip,shell=True,stdout=subprocess.PIPE)  # linux 系统将 '-n' 替换成 '-c',增加 'shell=True'
        # windows 系统，则使用 'ping -n 2 -w 5 $s'
        # 打印运行结果
        sql='update ipdb1.tp_ipdb set on_off_status=%s where ip="%s";'%(res,ip[0])
        my=mysql_io(host=host,user=user,passwd=passwd,db_name=dbname)
        my.update(sql)

#全部主机是否在线
def all_if_pc_off():
        my=mysql_io(host=host,user=user,passwd=passwd,db_name=dbname)
        ip_list=my.select_all("select ip from ipdb1.tp_ipdb;")
        print(ip_list)
        IP_QUEUE = Queue()
        for addr in ip_list:
            IP_QUEUE.put(addr)
        pool = ThreadPoolExecutor(max_workers=100)
        lock = threading.Lock()
        start_time = time.time()
        all_task = []
        while not IP_QUEUE.empty():
            all_task.append(pool.submit(ping_ip, IP_QUEUE.get()))
        # 等待所有任务结束
        wait(all_task, return_when=ALL_COMPLETED)
        return ('更新在线完成-总耗时：%s' % (time.time() - start_time))


#更新全部主机名
def update_pcname():
        my=mysql_io(host=host,user=user,passwd=passwd,db_name=dbname)
        ip_list=my.select_all("select ip from ipdb1.tp_ipdb;")
        IP_QUEUE = Queue()
        for addr in ip_list:
            IP_QUEUE.put(addr[0])
        pool = ThreadPoolExecutor(max_workers=20)
        lock = threading.Lock()
        start_time = time.time()
        all_task = []
        print (11)
        while not IP_QUEUE.empty():
            all_task.append(pool.submit(ip_getname_mysq, IP_QUEUE.get()))
        # 等待所有任务结束
        wait(all_task, return_when=ALL_COMPLETED)
        return ('更新mac完成-总耗时：%s' % (time.time() - start_time))
# 更新全部mac
def update_mac():
        my=mysql_io(host=host,user=user,passwd=passwd,db_name=dbname)
        ip_list=my.select_all("select ip from ipdb1.tp_ipdb;")
        IP_QUEUE = Queue()
        for addr in ip_list:
            IP_QUEUE.put(addr[0])
        pool = ThreadPoolExecutor(max_workers=20)
        lock = threading.Lock()
        start_time = time.time()
        all_task = []
        print (11)
        while not IP_QUEUE.empty():
            all_task.append(pool.submit(ip_getmac_mysql, IP_QUEUE.get()))
        # 等待所有任务结束
        wait(all_task, return_when=ALL_COMPLETED)
        return ('更新mac完成-总耗时：%s' % (time.time() - start_time))

#获取主机名
def ip_getname(ipaddr):
    HOSTNAME2 = socket.getfqdn(ipaddr)
    return HOSTNAME2

#获取主机名带主机写入
def ip_getname_mysq(ipaddr):
    HOSTNAME2 = socket.getfqdn(ipaddr)
    sql='update ipdb1.tp_ipdb set pc_name="%s" where ip="%s";'%(HOSTNAME2,ipaddr)
    print (sql)
    my=mysql_io(host=host,user=user,passwd=passwd,db_name=dbname)
    my.update(sql)
    return "更新成功"

#读取xls文件
def readxls(filepath):
    wb = xlrd.open_workbook(filepath)
    sh = wb.sheet_by_name('Sheet1')
    hang=sh.nrows
    lie=sh.ncols
    ip_l=[]
    name_l=[]
    tag_l=[]
    xy_l=[]
    for c in range(1,hang):
        ip_l.append(sh.cell(c,0).value)
        name_l.append(sh.cell(c,1).value)
        tag_l.append(sh.cell(c,2).value)
        xy_l.append(sh.cell(c,3).value)
    return zip(ip_l,name_l,tag_l,xy_l)

host="127.0.0.1"
user="root"
dbname="ipdb1"
passwd="123456"